package com.engc.stumng.web.newStudent;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.engc.common.service.BaseService;
import com.engc.common.utils.BaseUtils;

/**
 * 新生电子档案登记
 * 
 * @author gxx
 * 
 */
@Controller
@RequestMapping("/stu")
public class StuController {

	@Resource
	private BaseService baseService;

	@RequestMapping("/view")
	public String view(HttpServletRequest request) {
		return "person/stu";
	}
	@RequestMapping("/import")
	public String import1(HttpServletRequest request) {
		return "person/user";
	}

	@RequestMapping("/getNames")
	@ResponseBody
	public List<Map<String, Object>> getUser(HttpServletRequest request)
	{
		String sql = "select id,name text from t_auth_info where auth_status=1 and id not in (select auth_id from t_stu_info)";
		
		List<Map<String, Object>> list = baseService.Query(sql);
		
		return list;
	}
	
	/**
	 * 新增
	 * @param request
	 * @return
	 */
	@RequestMapping("/insert")
	@ResponseBody
	public Map<String, Object> insert(HttpServletRequest request) {
		Map<String, Object> record = BaseUtils.getInsertParam(request);
		Map<String, Object> authRecord = new HashMap<String, Object>();
		try {
			authRecord.put("id",System.currentTimeMillis());
			authRecord.put("idcode",record.get("idcode"));
			record.remove("idcode");
			authRecord.put("zkzcode",record.get("zkzcode"));
			record.remove("zkzcode");
			authRecord.put("letter",record.get("letter"));
			record.remove("letter");
			authRecord.put("name",record.get("name"));
			record.remove("name");
			authRecord.put("auth_status","3");
			
			record.put("id",System.currentTimeMillis());
			
			record.put("auth_id",authRecord.get("id"));
			
			baseService.Insert("t_auth_info", authRecord);
			
			baseService.Insert("t_stu_info", record);
			
			return BaseUtils.getSuccessMessage("登记成功");
		} catch (Exception e) {
			return BaseUtils.getErrorMessage("登记失败");
		}
	}
	
	/**
	 * 修改认证信息
	 * 
	 * @param request
	 * @return
	 */
	@RequestMapping("/update")
	@ResponseBody
	public Map<String, Object> update(HttpServletRequest request) {
		Map<String, Object> record = BaseUtils.getUpdateParam(request);

		try {
			baseService.Update("t_stu_info", record, "id=?", record.get("id"));
			return BaseUtils.getSuccessMessage("修改成功");
		} catch (Exception e) {
			return BaseUtils.getErrorMessage("修改失败");
		}
	}
	
	/**
	 * 删除
	 * @param request
	 * @return
	 */
	@RequestMapping("/delete")
	@ResponseBody
	public Map<String, Object> delete(HttpServletRequest request) {
		String id = request.getParameter("id");
		
		try {
			baseService.Delete("t_stu_info", "id=?",id);
			return BaseUtils.getSuccessMessage("删除成功");
		} catch (Exception e) {
			return BaseUtils.getErrorMessage("删除失败");
		}
	}
	
	
	@RequestMapping("/doimport")
	@ResponseBody
	@Transactional
	public Map<String, Object> doimport(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		Workbook rwb = null;
		Sheet sheet = null;
		Map<String,Object> data = null;
		StringBuffer sb = new StringBuffer();
		try {
			// 最大文件大小
			long maxSize = 5 * 1024 * 1024;
			// 定义允许上传的文件扩展名
			HashMap<String, String> extMap = new HashMap<String, String>();
			extMap.put("doctype", "xls,xlsx");
			DiskFileItemFactory factory = new DiskFileItemFactory();
			ServletFileUpload upload = new ServletFileUpload(factory);
			upload.setHeaderEncoding("UTF-8");// 解决文件乱码问题
			request.getParameter("fileupload2");
			List<FileItem> items = upload.parseRequest(request);
			Iterator<FileItem> itr = items.iterator();// 所有的表单项
			while (itr.hasNext()) {
				FileItem item = (FileItem) itr.next();// 循环获得每个表单项
				if (!item.isFormField()) {// 如果是文件类型
					String name = item.getName();// 获得文件名 包括路径
					long size = item.getSize();
					// 检查文件大小
					if (size > maxSize) {
						return BaseUtils.getErrorMessage("上传文件大小超过限制，最大为5M");
					}
					// 检查扩展名
					String fileExt = name.substring(name.lastIndexOf(".") + 1)
							.toLowerCase();
					if (!Arrays.<String> asList(
							extMap.get("doctype").split(",")).contains(fileExt)) {
						return BaseUtils
								.getErrorMessage("上传文件扩展名是不允许的扩展名。\n只允许"
										+ extMap.get("doctype") + "格式。");
					}

					// 获取
					rwb = Workbook.getWorkbook(item.getInputStream());
					// 获取第一张sheet
					sheet = rwb.getSheet(0);
					if (sheet == null || !sheet.getName().equals("JFDX")) {
						return BaseUtils.getErrorMessage("无法读取文件,请重新下载模板");
					}
					// 获取Sheet表中所包含的总列数
					int sheetColumns = sheet.getColumns();
					// 获取Sheet表中所包含的总行数
					int sheetRows = getRightRows(sheet);

					if (sheetRows <= 4 || sheetColumns < 2) {
						return BaseUtils.getErrorMessage("导入excel文件无数据");
					}
					if (sheetRows > Integer.valueOf(5000 + 6)) {
						return BaseUtils.getErrorMessage("一次性批量导入不能超过" + 5000
								+ "条数据");
					}

					String class_,username,idcode,zkzcode,letter,jiguan,contact,score,family_contact= null;
					Map<String,Object> basicRecord = null;
					Map<String,Object> authRecord = null;
					List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
					List<Map<String,Object>> authList = new ArrayList<Map<String,Object>>();
					for (int i = 5; i <= sheetRows; i++) {
						basicRecord = new HashMap<String,Object> ();
						authRecord = new HashMap<String,Object> ();
						String str = "";
						class_ = sheet.getCell(0, i).getContents().trim();
						username = sheet.getCell(1, i).getContents().trim();
						idcode = sheet.getCell(2, i).getContents().trim();
						zkzcode = sheet.getCell(3, i).getContents().trim();
						letter = sheet.getCell(4, i).getContents().trim();
						jiguan = sheet.getCell(5, i).getContents().trim();
						contact = sheet.getCell(6, i).getContents().trim();
						score = sheet.getCell(7, i).getContents().trim();
						family_contact = sheet.getCell(8, i).getContents().trim();
						if (StringUtils.isEmpty(class_))
							str += "班级不能为空 ";
						if (StringUtils.isEmpty(username))
							str += "姓名不能为空 ";
						if (StringUtils.isEmpty(idcode))
							str += "身份证不能为空 ";
						if (StringUtils.isEmpty(zkzcode))
							str += "准考证不能为空 ";
						Map<String,Object> classMap =
								baseService.Read("select t.* from t_study_info t where t.name=?",class_);
						if(classMap!=null&&classMap.size()>0&&!"".equals(classMap.get("id").toString())){
							class_=classMap.get("id").toString();
						}else{
							str += "【"+class_+"】班级未找到,请输入正确班级名称";
						}
						
						if (StringUtils.isNotEmpty(str)) {
							sb.append(" 第【" + (i + 1) + "】行,导入失败,失败原因:").append(
									str);
							return BaseUtils.getErrorMessage(sb.toString());
						}
						int a = (int) (Math.random()*(100-1+1)+1);
						authRecord.put("id",System.currentTimeMillis()+a);
						authRecord.put("idcode",idcode);
						authRecord.put("zkzcode",zkzcode);
						authRecord.put("letter",letter);
						authRecord.put("name",username);
						authRecord.put("auth_status","3");
						
						basicRecord.put("id",System.currentTimeMillis()+a);
						basicRecord.put("auth_id",authRecord.get("id"));
						basicRecord.put("class_",class_);
						basicRecord.put("is_single","2");
						basicRecord.put("score",score);
						basicRecord.put("family_contact",family_contact);
						basicRecord.put("contact",contact);
						basicRecord.put("jiguan",jiguan);
						mapList.add(basicRecord);
						authList.add(authRecord);
					}
				
					for (Map<String,Object>  authMap : authList) {
						baseService.Insert("t_auth_info", authMap);
					}
					// 批量插入
					for (Map<String,Object>  basicMap : mapList) {
						baseService.Insert("t_stu_info", basicMap);
					}
					
					data = new HashMap<String, Object>();
					data.put("Total", mapList.size());
					data.put("Rows", mapList);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return BaseUtils.getSuccessMessage(sb.length() > 0 ? sb.toString()
				: "导入成功", data);
	}
	
	//去除空行
	public  int getRightRows(Sheet sheet) {
		int rsCols = sheet.getColumns(); // 列数
		int rsRows = sheet.getRows(); // 行数
		int nullCellNum;
		int afterRows = rsRows;
		for (int i = 1; i < rsRows; i++) { // 统计行中为空的单元格数
			nullCellNum = 0;
			for (int j = 0; j < rsCols; j++) {
				String val = sheet.getCell(j, i).getContents();
				val = StringUtils.trimToEmpty(val);
				if (StringUtils.isBlank(val))
					nullCellNum++;
			}
			if (nullCellNum >= rsCols) { // 如果nullCellNum大于或等于总的列数
				afterRows--; // 行数减一
			}
		}
		return afterRows;
	}
	public static void main(String[] args) {
		int a = (int) (Math.random()*(100-1+1)+1);
		System.out.println(a);
	}
}
